<?php 

/**
 * Ganglib
 *
 * LICENSE
 *
 * This source file is subject to the GNU Lesser Generak Public license.
 * 
 * It is available through the world-wide-web at this URL:
 * http://www.gnu.org/licenses/lgpl.html
 *
 * @category    Ganglib
 * @package    Div_Db_Query_Dialect
 * @copyright  Copyright (c) 2008 Ganglib project members (http://www.code.google.com/p/ganglib)
 * @license     http://www.gnu.org/licenses/lgpl.html
 */


/**
 * Implementation of Query Driver for Mysql Database
 * 
 * @package    Div_Db_Query_Dialect
 * @copyright  Copyright (c) 2008 Ganglib project members (http://www.code.google.com/p/ganglib)
 * @license     http://www.gnu.org/licenses/lgpl.html
 */
class Div_Db_Query_Dialect_Mysql implements Div_Db_Query_Dialect_Interface {

	/**
	 * Instance of Div_Db_Query_Dialect_Mysql - singleton
	 */
	private static $instance = null;
	
	
	/**
	 * Return singleton instance
	 */
	public static function getInstance()
	{
		if(self :: $instance == null)
			self :: $instance = new Div_Db_Query_Dialect_Mysql();
			
		return self :: $instance;
	}

	/**
	 * Escape table name to use in SQL Query
	 * 
	 * @param string $table table name
	 * @return escaped table name 
	 */
	public function escape_table($table)
	{
		return '`'.str_replace('.', '`.`', $table).'`';
	}

	/**
	 * Escape sql table column name to use in SQL Query
	 * 
	 * @param string $column column name
	 * @return escaped column name
	 */
	public function escape_column($column)
	{
		if (strncasecmp(strtolower($column), 'count', 5) === 0 OR $column == '*')
			return $column;

		// This matches any modifiers we support to SELECT.
		if ( ! preg_match('/\b(?:rand|all|distinct(?:row)?|high_priority|sql_(?:small_result|b(?:ig_result|uffer_result)|no_cache|ca(?:che|lc_found_rows)))\s/i', $column))
		{
			if (stripos($column, ' AS ') !== FALSE)
			{
				// Force 'AS' to uppercase
				$column = str_ireplace(' AS ', ' AS ', $column);

				// Runs escape_column on both sides of an AS statement
				$column = array_map(array($this, __FUNCTION__), explode(' AS ', $column));

				// Re-create the AS statement
				return implode(' AS ', $column);
			}

			return preg_replace('/[^.*]+/', '`$0`', $column);
		}

		$parts = explode(' ', $column);
		$column = '';

		for ($i = 0, $c = count($parts); $i < $c; $i++)
		{
			// The column is always last
			if ($i == ($c - 1))
			{
				$column .= preg_replace('/[^.*]+/', '`$0`', $parts[$i]);
			}
			else // otherwise, it's a modifier
			{
				$column .= $parts[$i].' ';
			}
		}
		return $column;
	}

	/**
	 * Return REGEXP fragment - for DBMS specified dialect 
	 */
	public function regex($field, $match = '', $type = 'AND ', $num_regexs)
	{
		$prefix = ($num_regexs == 0) ? '' : $type;

		return $prefix.' '.$this->escape_column($field).' REGEXP \''.$this->escape_str($match).'\'';
	}

	/**
	 * Return NOT REGEXP fragment - for RDBMS specified dialect
	 */
	public function notregex($field, $match = '', $type = 'AND ', $num_regexs)
	{
		$prefix = $num_regexs == 0 ? '' : $type;

		return $prefix.' '.$this->escape_column($field).' NOT REGEXP \''.$this->escape_str($match) . '\'';
	}

	public function merge($table, $keys, $values)
	{
		// Escape the column names
		foreach ($keys as $key => $value)
		{
			$keys[$key] = $this->escape_column($value);
		}
		return 'REPLACE INTO '.$this->escape_table($table).' ('.implode(', ', $keys).') VALUES ('.implode(', ', $values).')';
	}

	/**
	 * Limit records in sql query
	 * 
	 * @param int $limit how many records
	 * @param int $offset starting record number
	 */
	public function limit($limit, $offset = 0)
	{
		return 'LIMIT '.$offset.', '.$limit;
	}

	/**
	 * Compile SELECT query from specified data and return query string
	 * 
	 * @param array $parts query parts returned from Div_Db_Query_Dialect::asArray
	 */
	public function compile_select($parts)
	{
		$sql = ($parts['distinct'] == TRUE) ? 'SELECT DISTINCT ' : 'SELECT ';
		$sql .= (count($parts['select']) > 0) ? implode(', ', $parts['select']) : '*';

		if (count($parts['from']) > 0)
		{
			$sql .= "\nFROM ";
			$sql .= implode(', ', $parts['from']);
		}

		if (count($parts['join']) > 0)
		{
			$sql .= ' '.implode("\n", $parts['join']);
		}

		if (count($parts['where']) > 0)
		{
			$sql .= "\nWHERE ";
		}

		$sql .= implode("\n", $parts['where']);

		if (count($parts['groupby']) > 0)
		{
			$sql .= "\nGROUP BY ";
			$sql .= implode(', ', $parts['groupby']);
		}

		if (count($parts['having']) > 0)
		{
			$sql .= "\nHAVING ";
			$sql .= implode("\n", $parts['having']);
		}

		if (count($parts['orderby']) > 0)
		{
			$sql .= "\nORDER BY ";
			$sql .= implode(', ', $parts['orderby']);
		}

		if (is_numeric($parts['limit']))
		{
			$sql .= "\n";
			$sql .= $this->limit($parts['limit'], $parts['offset']);
		}

		return $sql;
	}

	/**
	 * Compile UPDATE query from specified data and return query string
	 * 
	 * @param array $parts query parts returned from Rapid_Driver_Mysql::asArray
	 */	
	public function compile_update($parts)
	{
		$sql = 'UPDATE ';

		if (count($parts['from']) > 0)
		{
			$sql .= implode(', ', $parts['from']);
		}

		if (count($parts['set']) > 0)
		{
			foreach($parts['set'] as $key => $val)
			{
				$valstr[] = $this->escape_column($key).' = '.$val;
			}
			$sql .= ' SET '.implode(", ", $valstr);
		}

		if (count($parts['where']) > 0)
		{
			$sql .= "\nWHERE ";
		}

		$sql .= implode("\n", $parts['where']);

		return $sql;
	}
	
	/**
	 * Compile DELETE query from specified data and return query string
	 * 
	 * @param array $parts query parts returned from Rapid_Driver_Mysql::asArray
	 */	
	public function compile_delete($parts)
	{
		$sql = 'DELETE FROM ';

		if (count($parts['from']) > 0)
		{
			$sql .= implode(', ', $parts['from']);
		}

		if (count($parts['where']) > 0)
		{
			$sql .= "\nWHERE ";
		}

		$sql .= implode("\n", $parts['where']);

		return $sql;
	}	
		
		
	/**
	 * Compile INSERT query from specified data and return query string
	 * 
	 * @param array $parts query parts returned from Rapid_Driver_Mysql::asArray
	 */		
	public function compile_insert($parts)
	{
		$sql = 'INSERT INTO ';

		if (count($parts['from']) > 0)
		{
			$sql .= implode(', ', $parts['from']);
		}
		
		$sql.='(';

		if (count($parts['set']) > 0)
		{
			foreach($parts['set'] as $key => $val)
			{
				$valstr[] = $val;
				$keystr[] = $this->escape_column($key);
			}
			$sql .= implode(", ", $keystr);
		}

		$sql.=') VALUES (';
		$sql .= implode(", ", $valstr);
		$sql .= ');';

		return $sql;
	}		

	/**
	 * Escape string value in database-specific manner
	 * 
	 * @param string $str value to escape
	 * @return string escaped value
	 */
	public function escape_str($str)
	{
		return mysql_escape_string($str);
	}

		/**
	 * Builds a WHERE portion of a query.
	 *
	 * @param  mixed  $key
	 * @param  string $value
	 * @param  string $type
	 * @param  int    $num_wheres
	 * @param  bool   $quote
	 * @return string
	 */
	public function where($key, $value, $type, $num_wheres, $quote)
	{
		$prefix = ($num_wheres == 0) ? '' : $type;

		if ($quote === -1)
		{
			$value = '';
		}
		else
		{
			if ($value === NULL)
			{
				if ( ! $this->has_operator($key))
				{
					$key .= ' IS';
				}

				$value = ' NULL';
			}
			elseif (is_bool($value))
			{
				if ( ! $this->has_operator($key))
				{
					$key .= ' =';
				}

				$value = ($value == TRUE) ? ' 1' : ' 0';
			}
			else
			{
				if ( ! $this->has_operator($key))
				{
					$key = $this->escape_column($key).' =';
				}
				else
				{
					preg_match('/^(.+?)([<>!=]+|\bIS(?:\s+NULL))\s*$/i', $key, $matches);
					$key = $this->escape_column(trim($matches[1])).' '.trim($matches[2]);
				}

				$value = ' '.(($quote == TRUE) ? $this->escape($value) : $value);
			}
		}

		return $prefix.$key.$value;
	}

	/**
	 * Builds a LIKE portion of a query.
	 *
	 * @param  mixed  $field
	 * @param  string $match
	 * @param  string $type
	 * @param  int    $num_likes
	 * @return string
	 */
	public function like($field, $match = '', $type = 'AND ', $num_likes)
	{
		$prefix = ($num_likes == 0) ? '' : $type;

		$match = (substr($match, 0, 1) == '%' OR substr($match, (strlen($match)-1), 1) == '%') 
		       ? $this->escape_str($match) 
		       : '%'.$this->escape_str($match).'%';

		return $prefix.' '.$this->escape_column($field).' LIKE \''.$match . '\'';
	}
	
	/**
	 * Builds a IN() part of query
	 * 
	 * @param string $field name
	 * @param array $values
	 * @param string type AND or OR
	 * @param int $num_where how many where conditions we hvave?
	 */
	public function in($field,  $values, $type= 'AND', $num_where )
	{
		$prefix = ($num_where == 0) ? '' : $type;
		if(is_array($values))
		{
			$escaped_values = array();
			foreach($values as $value)
			{
				$escaped_values[] = $this->escape($value);
			}
		} else $escaped_values =$values;
		
		return $prefix.' '.$this->escape_column($field).' IN ('.(is_array($escaped_values)?implode(',', $escaped_values):$escaped_values) .')';
	}

	/**
	 * Builds a NOT IN() part of query
	 * 
	 * @param string $field name
	 * @param array $values
	 * @param string type AND or OR
	 * @param int $num_where how many where conditions we have
	 */
	public function notin($field,  $values, $type= 'AND',  $num_where )
	{
		$prefix = ($num_where == 0) ? '' : $type;
		if(is_array($values))
		{
			$escaped_values = array();
			foreach($values as $value)
			{
				$escaped_values[] = $this->escape($value);
			}
		} else $escaped_values =$values;
				
		return $prefix.' '.$this->escape_column($field).' NOT IN ('.(is_array($escaped_values)?implode(',', $escaped_values):$escaped_values) .')';
		
	}	

	/**
	 * Builds a NOT LIKE portion of a query.
	 *
	 * @param  mixed  $field
	 * @param  string $match
	 * @param  string $type
	 * @param  int    $num_likes
	 * @return string
	 */
	public function notlike($field, $match = '', $type = 'AND ', $num_likes)
	{
		$prefix = ($num_likes == 0) ? '' : $type;

		$match = (substr($match, 0, 1) == '%' OR substr($match, (strlen($match)-1), 1) == '%') 
		       ? $this->escape_str($match) 
		       : '%'.$this->escape_str($match).'%';

		return $prefix.' '.$this->escape_column($field).' NOT LIKE \''.$match.'\'';
	}  
	
	/**
	 * Determines if the string has an arithmetic operator in it.
	 *
	 * @param  string $str
	 * @return boolean
	 */
	public function has_operator($str)
	{
		return (bool) preg_match('/[<>!=]|\sIS\s+(?:NOT\s+)?NULL\b/i', trim($str));
	}

	/**
	 * Escapes any input value
	 *
	 * @param  mixed $value
	 * @param bool $quote if result should be quoted in ''
	 * @return string
	 */
	public function escape($value, $quote = true)
	{
		switch (gettype($value))
		{
			case 'string':
				if($quote)
					$value = '\''.$this->escape_str($value).'\'';
				else return $this->escape_str($value);
			break;
			case 'boolean':
				$value = (int) $value;
			break;
			case 'double':
				$value = sprintf('%F', $value);
			break;
			default:
				$value = ($value === NULL) ? 'NULL' : $value;
			break;
		}

		return (string) $value;
	}	
} 

